package com.wh1200.mybatis.xmlless.dialect

import com.wh1200.mybatis.xmlless.constant.PGSQL_LIMIT
import com.wh1200.mybatis.xmlless.constant.PGSQL_PAGEABLE_LIMIT
import com.wh1200.mybatis.xmlless.constant.Strings
import com.wh1200.mybatis.xmlless.model.FieldMapping
import com.wh1200.mybatis.xmlless.model.base.BaseClassWrapper
import com.wh1200.mybatis.xmlless.resolver.TypeResolver
import com.wh1200.mybatis.xmlless.util.TableInfoProvider.getOrInitXmllessTableInfo
import net.sf.jsqlparser.expression.Function
import net.sf.jsqlparser.expression.StringValue

/**
 * PostgreSQL方言
 *
 * @author 吴昊
 * @date 2023/12/18
 * @version 1.0
 * @since v4.0.0
 */
object PostgreSQLDialect : IDialect {

  override fun toLimitSql(
      isSpringDataPageable: Boolean,
      pageableParamName: String?,
      offsetParam: String,
      sizeParam: String
  ): String {
    if (isSpringDataPageable) {
      return PGSQL_PAGEABLE_LIMIT.format(
          pageableParamName, sizeParam, offsetParam
      )
    }
    return PGSQL_LIMIT.format(sizeParam, offsetParam)
  }

  override fun createJsonArrayElementEqual(columnResult: String, param: String): String {
    return """(%s)::jsonb @> ${'$'}{"'[" + ${Strings.jsonCall("%s")} + "]'"}::jsonb""".format(
        columnResult, param
    )
  }

  override fun createJoinJsonArrayElementEqual(column: String, value: String): String {
    return """(%s)::jsonb @> (%s)::jsonb""".format(column, value)
  }

  override fun getEqMonthConditionTemplate(): String {
    return "TO_CHAR(%s, 'YYYY-MM') %s TO_CHAR(%s, 'YYYY-MM')"
  }

  override fun getEqDateConditionTemplate(): String {
    return "TO_CHAR(%s, 'YYYY-MM-DD') %s TO_CHAR(%s, 'YYYY-MM-DD')"
  }

  override fun createJsonRemoveExpression(column: String, parameterName: String, isNumberType: Boolean): String {
    if (isNumberType) {
      return """(
  SELECT jsonb_agg(value)
  FROM jsonb_array_elements(cast($column as jsonb)) AS value
  WHERE CAST(value AS text) &lt;&gt; CONCAT('', #{${parameterName}}, ''))"""
    }
    return """(
  SELECT jsonb_agg(value)
  FROM jsonb_array_elements(cast($column as jsonb)) AS value
  WHERE CAST(value AS text) &lt;&gt; concat('"', #{${parameterName}}, '"')
)"""
  }

  override fun createJsonAddExpression(column: String, parameterName: String): String {
    val valueExp = """ CAST(${'$'}{"'" + ${Strings.jsonCall(parameterName)} + "'"} AS JSONB)"""
    return "JSONB_INSERT(CAST($column AS jsonb), '{10000}', $valueExp)"
  }

  override fun replaceDateFunction(function: Function) {
    val functionName = function.name.lowercase()
    function.name = "TO_CHAR"
    when (functionName) {
      "year"  -> {
        function.parameters.addExpressions(
            StringValue("YYYY")
        )
      }

      "month" -> {
        function.parameters.addExpressions(
            StringValue("YYYY-MM")
        )
      }

      "date"  -> {
        function.parameters.addExpressions(
            StringValue("YYYY-MM-DD")
        )
      }

      else    -> error("")
    }
  }

  override fun createJsonArrayElementIn(columnResult: String, paramOrValue: String, value: Boolean): String {
    if (value) {
      return """(%s)::jsonb @> ANY(ARRAY[%s])""".format(
          columnResult, paramOrValue
      )
    }
    return """(%s)::jsonb @> ANY(ARRAY[<foreach collection="%s" item="el" separator=",">'[${'$'}{${Strings.jsonCall("el")}}]'::jsonb</foreach>])""".format(
        columnResult, paramOrValue
    )
  }

  override fun toJsonPath(parentMapping: FieldMapping, relativePath: String): String {
    val castType = resolveCastType(parentMapping, relativePath)
    val columnExp = parentMapping.column + "->>'" + if (parentMapping.isJsonArray) {
      "$[*].$relativePath"
    } else {
      relativePath
    } + "'"
    if (castType != null) {
      return "($columnExp)::$castType"
    }
    return columnExp
  }

  override fun getInsertOrUpdateTemplate(): String {
    return """INSERT INTO
  %s(%s)
VALUES 
  (%s)
ON CONFLICT (id) DO UPDATE SET
  %s"""
  }

  override fun getBatchInsertOrUpdateTemplate(): String {
    return """INSERT INTO
  %s(%s)
VALUES
  <foreach collection="list" item="item" separator=",">
    (%s)
  </foreach>
ON CONFLICT (id) DO UPDATE SET
  %s """
  }

  override fun createUpsertFieldSetExpression(column: String): String {
    return "EXCLUDED.$column"
  }

  override fun wrapColumnIfMatchKeywords(column: String): String {
    return "\"$column\""
  }

  private fun resolveCastType(
      parentMapping: FieldMapping,
      relativePath: String
  ): String? {
    var type = TypeResolver.resolveRealType(parentMapping.type)
    val wrapperClass = Class.forName("com.wh1200.mybatis.xmlless.JavaClassWrapper")
    if (!Map::class.java.isAssignableFrom(type)) {
      relativePath.split(".").forEach {
        val tableInfo = getOrInitXmllessTableInfo(
            wrapperClass.constructors[0].newInstance(type) as BaseClassWrapper
        )
        val mapping = tableInfo.tableFieldMap[it] ?: return null
        type = mapping.genericType.rawType
      }
    }
    val castType = when (type.name) {
      "java.lang.Long", "long",
      "java.lang.Integer", "int"     -> "integer"
      "java.lang.String"             -> "text"
      "java.lang.Boolean", "boolean" -> "boolean"
      else                           -> null
    }
    return castType
  }

}
